package cn.tedu.baidashi.mapper;

import cn.tedu.baidashi.model.City;
import cn.tedu.baidashi.vo.CityVo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
* <p>
    *  Mapper 接口
    * </p>
*
* @author tedu.cn
* @since 2022-02-16
*/
    @Repository
    public interface CityMapper extends BaseMapper<City> {
        @Select("SELECT city.id,city.city_name FROM city  LEFT JOIN province_city ON city.id=province_city.city_id\n" +
                "LEFT JOIN province ON province.id=province_city.province_id WHERE province.id=#{id}")
        List<CityVo> selectCityNamesByProvinceId(Integer id);
        @Select("SELECT city.city_name,city.id FROM city " +
                "LEFT JOIN province_city ON city.id=province_city.city_id " +
                "WHERE city.id in(SELECT city_id FROM province_city " +
                "LEFT JOIN province ON province.id=province_city.province_id " +
                "WHERE province_id=(SELECT province.id FROM province " +
                "WHERE province.province_name=#{province_name}));")
        List<CityVo> selectCityNamesByProvinceName(String province_name);
    }
